package com.example.test.poi;

import lombok.Cleanup;
import lombok.SneakyThrows;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.util.Date;

public class POIWriteExcel {
    public static void main(String[] args) {
        test05();
    }

    /**
     * 03版
     **/
    @SneakyThrows
    private static void test01() {
        // 1. 创建一个工作簿
        Workbook wb = new HSSFWorkbook();
        // 2. 创建一个工作表
        Sheet sheet0 = wb.createSheet("表1");
        // 3. 创建一个行
        Row row00 = sheet0.createRow(0);
        // 4. 创建一个单元格, 字符串
        Cell cell000 = row00.createCell(0);
        cell000.setCellValue("aaa");  // 单元格赋值
        // 5. 创建一个单元格, 日期
        Cell cell001 = row00.createCell(1);
        cell001.setCellValue(String.format("%tF %<tT",new Date()));
        // 6. 生成文件
        @Cleanup
        FileOutputStream fos = new FileOutputStream("D:/tmp" + "/test.xls");
        wb.write(fos);
    }

    /**
     * 07版
     **/
    @SneakyThrows
    private static void test02() {
        // 1. 创建一个工作簿
        Workbook wb = new XSSFWorkbook();
        // 2. 创建一个工作表
        Sheet sheet0 = wb.createSheet("表1");
        // 3. 创建一个行
        Row row00 = sheet0.createRow(0);
        // 4. 创建一个单元格, 字符串
        Cell cell000 = row00.createCell(0);
        cell000.setCellValue("aaa");  // 单元格赋值
        // 5. 创建一个单元格, 日期
        Cell cell001 = row00.createCell(1);
        cell001.setCellValue(String.format("%tF %<tT",new Date()));
        // 6. 生成文件
        @Cleanup
        FileOutputStream fos = new FileOutputStream("D:/tmp" + "/test.xlsx");
        wb.write(fos);
    }

    /**
     *  03版大量写，1.569s
     **/
    @SneakyThrows
    private static void test03() {
        long begin = System.currentTimeMillis();

        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet();

        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }

        @Cleanup
        FileOutputStream fos = new FileOutputStream("D:/tmp" + "/test.xls");
        wb.write(fos);
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

    /**
     *  07版大量写，5.118s
     **/
    @SneakyThrows
    private static void test04() {
        long begin = System.currentTimeMillis();

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();

        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }

        @Cleanup
        FileOutputStream fos = new FileOutputStream("D:/tmp" + "/test.xlsx");
        wb.write(fos);
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

    /**
     *  07版大量写，SXSSFWorkbook，1.283s
     **/
    @SneakyThrows
    private static void test05() {
        long begin = System.currentTimeMillis();

        Workbook wb = new SXSSFWorkbook();
        Sheet sheet = wb.createSheet();

        for (int rowNum = 0; rowNum < 65536; rowNum++) {
            Row row = sheet.createRow(rowNum);
            for (int cellNum = 0; cellNum < 10; cellNum++) {
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }

        @Cleanup
        FileOutputStream fos = new FileOutputStream("D:/tmp" + "/test.xlsx");
        wb.write(fos);
        // 清理临时文件
        ((SXSSFWorkbook) wb).dispose();
        long end = System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }
}
